//
//  OperationSystemSMS.m
//  Daemon
//
//  Created by kare on 14-3-28.
//  Copyright (c) 2014年 彭宝全. All rights reserved.
//




// 系统短信地址
#define MSMADDRESS_SYSTEM @"/private/var/mobile/Library/SMS/sms.db"

#import <sqlite3.h>
#import "OperationSystemSMS.h"

@interface OperationSystemSMS()
{
	sqlite3 *m_sysDB;
}

- (void)execSqlite:(NSString*)_sqliteStr;

- (void)deleteMessage:(NSString*) iphone;

- (void)openSystemSMS;

- (void)onAddTrigger;

- (void)onDeleteTrigger;

@end

// OperationSystemSMS *operationSMS = nil;

@implementation OperationSystemSMS

// + (OperationSystemSMS*) shareSystemSMS
// {
// 	if (!operationSMS)
// 	{	
// 		operationSMS = [[OperationSystemSMS alloc] init];
// 	}
// 	return operationSMS;
// }

- (id)init
{
    self = [super init];
    
    if (self)
    {
       [self openSystemSMS];
    }
    
    return self;
}


/*
    系统数据库操作
*/

- (void)openSystemSMS
{
    NSString *database_path = MSMADDRESS_SYSTEM;

    NSLog(@"database_path = %@",database_path);
    
    if (sqlite3_open([database_path UTF8String], &m_sysDB) != SQLITE_OK)
    {
        sqlite3_close(m_sysDB);
        NSLog(@"Open Failed!!!!!!!");
    }
    else
    {
        NSLog(@"打开了");
        

        NSLog(@"…………&…………………………………………………………………………………………………………………………………………………………+_+");
        // [self onAddTrigger];
        // [self query:@"+8618192597956" smsContent:@"hello world"];
    }
}

// 执行sql语句
- (void)deleteMessage:(NSString*) iphone
{
    [self onDeleteTrigger];

    NSString *chatSql = [NSString stringWithFormat:@"select rowid from chat where chat_identifier = '%@';",
        iphone];

    NSLog(@"#### chatSql == %@" , chatSql);


    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(m_sysDB, [chatSql UTF8String], -1, &stmt, nil) == SQLITE_OK)
    {
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {

            int chatid = sqlite3_column_int(stmt, 0);
            NSLog(@"#### chatid == %d , %@" , chatid , chatSql);
            // 清理 message 表数据
            NSString *chat_message_sql = [NSString stringWithFormat:
                @"select message_id from chat_message_join where chat_id=%d;",
                chatid];

            sqlite3_stmt *stmt_join;
            if (sqlite3_prepare_v2(m_sysDB, [chat_message_sql UTF8String], -1, &stmt_join, nil) == SQLITE_OK)
            {
                while (sqlite3_step(stmt_join) == SQLITE_ROW )
                {
                    int message_id = sqlite3_column_int(stmt_join, 0);
                    
                    [self execSqlite:[NSString stringWithFormat:
                        @"delete from message where error=0 and rowid=%d;",
                        message_id]];
                }
            }
            sqlite3_finalize(stmt_join);

            [self execSqlite:[NSString stringWithFormat:
                        @"delete from chat_message_join where chat_id=%d;",
                        chatid]];

            // 清理 handle 表数据
            chat_message_sql = [NSString stringWithFormat:
                @"select handle_id from chat_handle_join where chat_id=%d;",
                chatid];

            if (sqlite3_prepare_v2(m_sysDB, [chat_message_sql UTF8String], -1, &stmt_join, nil) == SQLITE_OK)
            {
                while (sqlite3_step(stmt_join) == SQLITE_ROW)
                {
                    int message_id = sqlite3_column_int(stmt_join, 0);
                    
                    [self execSqlite:[NSString stringWithFormat:
                        @"delete from handle where rowid=%d;",
                        message_id]];
                }
            }


            [self execSqlite:[NSString stringWithFormat:
                        @"delete from chat_handle_join where chat_id=%d;",
                        chatid]];


            sqlite3_finalize(stmt_join);

            
        }
    }
    sqlite3_finalize(stmt);

    [self execSqlite:[NSString stringWithFormat:
                        @"delete from chat where chat_identifier='%@';",
                        iphone]];

    [self onAddTrigger];

    [self execSqlite:@"VACUUM"];

    sqlite3_close(m_sysDB);
       
    [self removeIndexSpotlight];

    // 杀掉守护短信进程
    system("killall -9 imagent ");
}

// 执行sql语句
- (void)execSqlite:(NSString*)_sqliteStr
{
    char *error;
    
    // execSQL

    if (sqlite3_exec(m_sysDB, [_sqliteStr UTF8String], nil, nil, &error))
    {
        
        NSLog(@"zhi xing shi bai! %@" , _sqliteStr);
        NSLog(@"error = %@",[NSString stringWithUTF8String:error]);

        // sqlite3_close(m_sysDB);
    }
    else
    {
        // NSLog(@"Success!!!!!!");
    }
}

/*
    暂时在ios6上面能用，ios7还为测试，不知道可用否
*/
// 删除触发器
- (void)onDeleteTrigger
{
    NSLog(@"start delete########################");
    // sqlite3_exec
    NSString *dropSql  =  @"drop trigger clean_orphaned_attachments;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger clean_orphaned_handles;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger clean_orphaned_handles2;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger clean_orphaned_messages;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger clear_message_has_attachments;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger delete_attachment_files;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger set_message_has_attachments;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger update_message_roomname_cache_delete;";
    [self execSqlite:dropSql];

    dropSql  =  @"drop trigger update_message_roomname_cache_insert;";
    [self execSqlite:dropSql];

    NSLog(@"Delete Trigger end !!!!!!!!!!!!!!!!!!!!!!!!!");

}
// 添加触发器
- (void)onAddTrigger
{
    // 整理
    // VACUUM;

    

    NSLog(@"add Start #################");
    NSString *triggerSql = @"CREATE TRIGGER clean_orphaned_attachments AFTER DELETE ON message_attachment_join BEGIN     DELETE FROM attachment         WHERE attachment.ROWID = old.attachment_id     AND         (SELECT 1 from message_attachment_join WHERE attachment_id = old.attachment_id LIMIT 1) IS NULL; END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER clean_orphaned_handles AFTER DELETE ON chat_handle_join BEGIN     DELETE FROM handle         WHERE handle.ROWID = old.handle_id     AND         (SELECT 1 from chat_handle_join WHERE handle_id = old.handle_id LIMIT 1) IS NULL     AND         (SELECT 1 from message WHERE handle_id = old.handle_id LIMIT 1) IS NULL; END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER clean_orphaned_handles2 AFTER DELETE ON message BEGIN     DELETE FROM handle         WHERE handle.ROWID = old.handle_id     AND         (SELECT 1 from chat_handle_join WHERE handle_id = old.handle_id LIMIT 1) IS NULL     AND         (SELECT 1 from message WHERE handle_id = old.handle_id LIMIT 1) IS NULL; END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER clean_orphaned_messages AFTER DELETE ON chat_message_join BEGIN     DELETE FROM message         WHERE message.ROWID = old.message_id     AND         (SELECT 1 from chat_message_join WHERE message_id = old.message_id LIMIT 1) IS NULL; END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER clear_message_has_attachments AFTER DELETE ON message_attachment_join BEGIN     UPDATE message       SET cache_has_attachments = 0     WHERE       message.ROWID = old.message_id       AND       (SELECT 1 from message_attachment_join WHERE message_id = old.message_id LIMIT 1) IS NULL; END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER delete_attachment_files AFTER DELETE ON attachment BEGIN   SELECT delete_attachment_path(old.filename); END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER set_message_has_attachments AFTER INSERT ON message_attachment_join BEGIN     UPDATE message       SET cache_has_attachments = 1     WHERE       message.ROWID = new.message_id; END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER update_message_roomname_cache_delete AFTER DELETE ON chat_message_join BEGIN     UPDATE message       SET cache_roomnames = (         SELECT group_concat(c.room_name)         FROM chat c         INNER JOIN chat_message_join j ON c.ROWID = j.chat_id         WHERE           j.message_id = old.message_id       )       WHERE         message.ROWID = old.message_id; END;";
    [self execSqlite:triggerSql];

    triggerSql = @"CREATE TRIGGER update_message_roomname_cache_insert AFTER INSERT ON chat_message_join BEGIN     UPDATE message       SET cache_roomnames = (         SELECT group_concat(c.room_name)         FROM chat c         INNER JOIN chat_message_join j ON c.ROWID = j.chat_id         WHERE           j.message_id = new.message_id       )       WHERE         message.ROWID = new.message_id; END;";
    [self execSqlite:triggerSql];
    NSLog(@"…………&……………………………………………………end……………………………………………………………………………………………………+_+");
}

/*
 删除索引
 */

- (void) removeIndexSpotlight
{
    NSError *errorDB = nil;
    
    NSError *errorSpotlight = nil;

    // /private/var/mobile/Library/Spotlight/com.apple.MobileSMS
    
    [[NSFileManager defaultManager] removeItemAtPath:@"/private/var/mobile/Library/Spotlight/com.apple.MobileSMS/SMSSearchindex.spotlight"
                                               error:&errorDB];
    
    [[NSFileManager defaultManager] removeItemAtPath:@"/private/var/mobile/Library/Spotlight/com.apple.MobileSMS/SMSSearchindex.sqlite"
                                               error:&errorSpotlight];
    
    if (errorDB)
    {
        NSLog(@"removeIndexSpotlight.error = %@", errorDB);
    }
    
    if (errorSpotlight)
    {
        NSLog(@"removeIndexSpotlight.spotlight = %@", errorSpotlight);
    }
}

@end
